package com.draemon.haixiawenti.repository;

import com.yangzcp.userpojo.B_zhanji;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * <p></p>
 *
 * @author  yangz
 * @date    2016/11/13
 * @version latest
 */
public interface B_zhanjiRepository extends JpaRepository<B_zhanji, Integer>{

    @Query("select distinct label from B_zhanji where ref_saizhong.id=?1")
    public List<String> findLabelGroup(Integer id);

    @Query("select ref_qiudui.id as qiudui_id, ref_qiudui.name as qiudui, ref_qiudui.logo as qiudui_logo, sum(changci) as changci, sum(sheng) as sheng, sum(pin) as pin, sum(fu) as fu, sum(jin) as jin, sum(shi) as shi, sum(jifen) as jifen from B_zhanji where ref_saizhong.id=?1 and label=?2 group by ref_qiudui order by sum(jifen) desc,sum(jin)-sum(shi) desc,sum(sheng)-sum(fu) desc")
    public List<Map<String, Object>> findCond(Integer id, String label);

    @Query("select ref_qiudui.id as qiudui_id, ref_qiudui.name as qiudui, ref_qiudui.logo as qiudui_logo, sum(changci) as changci, sum(sheng) as sheng, sum(pin) as pin, sum(fu) as fu, sum(jin) as jin, sum(shi) as shi, sum(jifen) as jifen from B_zhanji where ref_saizhong.id=?1 group by ref_qiudui order by sum(jifen) desc,sum(jin)-sum(shi) desc,sum(sheng)-sum(fu) desc")
    public List<Map<String, Object>> findCond(Integer id);
}
